from flask import Blueprint, jsonify, request, json
from Login.loginView import cursor
import pymysql as sql

old = Blueprint('old', __name__)

connect = sql.Connect(
    host='bj-cdb-hjbj4oh0.sql.tencentcdb.com',
    port=60670,
    user='root',
    passwd='Yuan2000_',
    db='tencentmysql',
    charset='utf8'
)
cursor = connect.cursor()


# 老人信息录入
@old.route('/addinfo_old', methods=['POST', 'GET'])
def addinfo_old():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    username_old = json_data.get('username')
    gender_old = json_data.get('gender')
    phone_old = json_data.get('phone')
    id_card = json_data.get('id_card')
    Age_old = json_data.get('birthday')
    room_number = json_data.get('room_number')
    sql1 = "INSERT INTO oldperson_info(username, gender ,phone, id_card, Age, room_number) " \
           "VALUES('" + \
           str(username_old) + "', '" + str(gender_old) + "', '" + str(phone_old) + "', '" + str(id_card) + "', '" + str(Age_old) + "',  '" + str(room_number) + "')"

    print(sql1)

    try:
        cursor.execute(sql1)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)


# 录入图像目录，录入头像路径，录入房间号
@old.route('/addinfo_guard', methods=['POST', 'GET'])
def addinfo_guard():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    username = json_data.get('username')
    guardname1 = json_data.get('guardname1')
    phone1 = json_data.get('phone1')
    guardname2 = json_data.get('guardname2')
    phone2 = json_data.get('phone2')
    # imgset_dir = json_data.get('imgset_dir')
    # profile_photo = json_data.get('profile_photo')

    sql2 = "UPDATE oldperson_info set firstguardian_name = '" + str(guardname1) + "', firstguardian_phone = '" + str(phone1) + "', secondguardian_name = '" + str(guardname2) + "'\
        , secondguardian_phone = '" + str(phone2) + "' where username = '" + str(username) + "'"
    print(sql2)
    try:
        cursor.execute(sql2)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)
    return jsonify(1)

    return jsonify(2)


# 查看老人监护人信息等
@old.route('/getinfo_guard', methods=['POST', 'GET'])
def getinfo_guard():
    data = request.get_data()
    # json_data = json.loads(data.decode("utf-8"))
    # username = json_data.get('username')
    # sql8 = "SELECT firstguardian_name, firstguardian_phone, secondguardian_name, secondguardian_phone FROM " \
    #        "oldperson_info where username = '" + username + "'"
    #
    # print(sql8)
    # attribute = ['firstguardian_name', 'firstguardian_phone', 'secondguardian_name', 'secondguardian_phone']
    # l = []
    # for item in cursor.fetchall():
    #     dic = dict(map(lambda x, y: [x, y], attribute, item))
    #     l.append(dic)
    # # print(l)
    # return jsonify(l)
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    username = json_data.get('username')
    sql8 = "SELECT firstguardian_name, firstguardian_phone, secondguardian_name, secondguardian_phone, DESCRIPTION FROM " \
           "oldperson_info where username = '" + username + "'"
    print(sql8)
    cursor.execute(sql8)
    attribute = ['firstguardian_name', 'firstguardian_phone', 'secondguardian_name', 'secondguardian_phone', 'description']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)


# 列举所有老人信息
@old.route('/listinfo_old', methods=['POST', 'GET'])
def listinfo_old():
    sql3 = "SELECT ID, username, gender, phone, room_number, Age ,imgset_dir FROM oldperson_info"
    print(sql3)
    cursor.execute(sql3)
    # rows = cursor.fetchall()
    # object_list = []
    # for row in rows:
        # result = {"ID": row[0], "username": row[3]}
        # object_list.append(result)
    # res = json.dumps(object_list)
    # return res
    attribute = ['ID', 'username', 'gender', 'phone',  'room_number', 'Age', 'imgset_dir']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)


# 查询老人信息
@old.route('/searchinfo_old', methods=['POST', 'GET'])
def searchinfo_old():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    username = json_data.get('username')
    sql4 = "SELECT ID, username, gender, phone, id_card, Age FROM oldperson_info where username= '" + username + "'"
    print(sql4)
    cursor.execute(sql4)
    attribute = ['ID', 'username', 'gender', 'phone', 'id_card', 'Age']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)


# 删除老人信息
@old.route('/delete_old', methods=['POST', 'GET'])
def delete_old():
    data = request.get_data()
    word = data.decode("utf-8")
    sql5 = "DELETE FROM oldperson_info WHERE username = '" + str(word) + "'"
    print(sql5)

    try:
        cursor.execute(sql5)
        connect.commit()
    except:
        return jsonify(2)
    else:
        return jsonify(1)


# 老人信息修改
@old.route('/modify_old', methods=['POST', 'GET'])
def modify_old():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    username = json_data.get('name')
    gender = json_data.get('gender')
    phone = json_data.get('phone')
    id_card = json_data.get('id_card')
    Age = json_data.get('Age')
    room_number = json_data.get('room_number')
    # 这里先只考虑修改名字
    value = json_data.get('value')
    sql6 = "UPDATE oldperson_info set gender = '" + str(gender) + "', phone = '" + str(phone) + "', id_card = '" + str(id_card) + "'\
    , Age = '" + str(Age) + "', room_number = '" + str(room_number) + "' where username = '" + str(username) + "'"
    print(sql6)
    try:
        cursor.execute(sql6)
        connect.commit()
    except:
        return jsonify(0)
    else:
        return jsonify(1)
    return jsonify(1)


# 模糊查询(以username为例)
@old.route('/search_old', methods=['POST', 'GET'])
def searchSpecData(word):
    sql7 = "SELECT  username, gender, phone, id_card, Age, room_number FROM oldperson_info WHERE username LIKE '%" + word + "%'"
    print(sql7)
    cursor.execute(sql7)

    attribute = ['username', 'gender', 'phone', 'id_card', 'Age', 'room_number']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)



@old.route('/select_description', methods=['POST', 'GET'])
def select_description():
    data = request.get_data()
    json_data = json.loads(data.decode("utf-8"))
    username = json_data.get('username')
    sql9 = "SELECT DESCRIPTION FROM oldperson_info where username = '" + username + "'"
    print(sql9)
    cursor.execute(sql9)
    attribute = ['description']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)


#返回老人id和年龄的接口
@old.route('/select_age', methods=['POST', 'GET'])
def select_age():
    sql10 = "SELECT ID, Age FROM oldperson_info"
    print(sql10)
    cursor.execute(sql10)
    attribute = ['ID', 'age']
    l = []
    for item in cursor.fetchall():
        dic = dict(map(lambda x, y: [x, y], attribute, item))
        l.append(dic)
    # print(l)
    return jsonify(l)
